package com.system.Tools;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.ImageUtils;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class Export_Excel {

    public static void export_Excel(HttpServletResponse response,
                                    String imgUrl,
                                    ArrayList<String> titleKeyList,
                                    Map<String, String> titleMap,
                                    List<Map<String, Object>> src_list) throws
            Exception {
        String xlsFile_name = "static" + ".xlsx";     //输出xls文件名称
        //内存中只创建100个对象
        Workbook wb = new SXSSFWorkbook(100);           //关键语句
        Sheet sheet = null;     //工作表对象
        Row nRow = null;        //行对象
        Cell nCell = null;      //列对象

        CellStyle style = wb.createCellStyle();
        //一、设置背景色：
        style.setFillForegroundColor((short) 13);// 设置背景色
        //style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 水平居中
        style.setBorderBottom(BorderStyle.THIN); // 下边框
        style.setBorderLeft(BorderStyle.THIN);// 左边框
        style.setBorderTop(BorderStyle.THIN);// 上边框
        style.setBorderRight(BorderStyle.THIN);// 右边框

        //图片转化成为字节
        byte[] byteImage = ImgTool.returnImgToBate(imgUrl);


        int rowNo = 0;      //总行号
        int pageRowNo = 0;  //页行号
        for (int k = 0; k < src_list.size(); k++) {
            Map<String, Object> srcMap = src_list.get(k);
            //写入300000条后切换到下个工作表
            if (rowNo % 300000 == 0) {
                wb.createSheet("工作簿" + (rowNo / 300000));//创建新的sheet对象
                sheet = wb.getSheetAt(rowNo / 300000);        //动态指定当前的工作表

                // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
                Drawing<?> patriarch = sheet.createDrawingPatriarch();
                //常量参数为边距与单元格格式可调整
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short) 0, 1, (short) 10, 16);
                patriarch.createPicture(anchor, wb.addPicture(byteImage, HSSFWorkbook.PICTURE_TYPE_JPEG));

                pageRowNo = 0;      //新建了工作表,重置工作表的行号为0
                // -----------定义表头-----------
                nRow = sheet.createRow(20+pageRowNo++);
                // 列数 titleKeyList.size()
                for (int i = 0; i < titleKeyList.size(); i++) {
                    Cell cell_tem = nRow.createCell(i);
                    cell_tem.setCellStyle(style);
                    cell_tem.setCellValue(titleMap.get(titleKeyList.get(i)));
                }
                rowNo++;
            }
            rowNo++;
            nRow = sheet.createRow(20+pageRowNo++);    //新建行对象

            // 行，获取cell值
            for (int j = 0; j < titleKeyList.size(); j++) {
                nCell = nRow.createCell(j);
                nCell.setCellStyle(style);
                if (srcMap.get(titleKeyList.get(j)) != null) {
                    nCell.setCellValue(srcMap.get(titleKeyList.get(j)).toString());
                } else {
                    nCell.setCellValue("");
                }
            }
        }
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + xlsFile_name);
        response.flushBuffer();
        OutputStream outputStream = response.getOutputStream();
        wb.write(response.getOutputStream());
        wb.close();
        outputStream.flush();
        outputStream.close();


    }
}
